package droid.insane.database;

import java.util.ArrayList;

import droid.insane.logic.ConstantValues;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class DBHandler {

	private static DBHandler dbHandlerInstance;

	// This array contains the column names of the data fields that should
	// be show in the market data window
	public final static String[] MARKET_DATA_COL = { DbConstants.COLUMN_SYMBOL,
			DbConstants.COLUMN_COMPANY_NAME,
			DbConstants.COLUMN_LAST_TRADED_PRICE, DbConstants.COLUMN_CHANGE_RS,
			DbConstants.COLUMN_CHANGE_PRECENTAGE };

	public static final String[] PORTFOLIO_COLUMNS = {
			DbConstants.COLUMN_PORTFOLIO_ID, DbConstants.COLUMN_STOCK,
			DbConstants.COLUMN_QUANTITY, DbConstants.COLUMN_COST };

	// Database fields
	private SQLiteDatabase database;
	private DBOpenHelper dbHelper;

	private DBHandler(Context context) {
		dbHelper = new DBOpenHelper(context);
	}

	/**
	 * @param context
	 *            class path
	 * @return DBHandler instance
	 */
	public static DBHandler getDBHandler(Context context) {
		if (dbHandlerInstance == null) {
			dbHandlerInstance = new DBHandler(context);
		}
		return dbHandlerInstance;
	}

	/**
	 * @throws SQLException
	 */
	public void openToWrite() throws SQLException {
		database = dbHelper.getWritableDatabase();
		Log.d(ConstantValues.TAG, "Database open");
	}

	/**
	 * @throws SQLException
	 */
	public void openToRead() throws SQLException {
		database = dbHelper.getReadableDatabase();
		Log.d(ConstantValues.TAG, "Database open");
	}

	/**
	 * close the data base connection
	 */
	public void close() {
		dbHelper.close();
		Log.d(ConstantValues.TAG, "Database closed");
	}

	/**
	 * @param fields
	 *            contains the data to be entered to the database
	 */
	public void marketDataEntry(String[] fields) {
		ContentValues cv = new ContentValues();
		cv.put(DbConstants.COLUMN_COMPANY_NAME, fields[0]);
		cv.put(DbConstants.COLUMN_SYMBOL, fields[1]);
		cv.put(DbConstants.COLUMN_SHARE_VOLUME, fields[2]);
		cv.put(DbConstants.COLUMN_TRADE_VOLUME, fields[3]);
		cv.put(DbConstants.COLUMN_PREV_CLOSE, fields[4]);
		cv.put(DbConstants.COLUMN_OPEN, fields[5]);
		cv.put(DbConstants.COLUMN_HIGH, fields[6]);
		cv.put(DbConstants.COLUMN_LOW, fields[7]);
		cv.put(DbConstants.COLUMN_LAST_TRADED_PRICE, fields[8]);
		cv.put(DbConstants.COLUMN_CHANGE_RS, fields[9]);
		cv.put(DbConstants.COLUMN_CHANGE_PRECENTAGE, fields[10]);
		cv.put(DbConstants.COLUMN_UP_OR_DOWN, fields[11]);

		database.insert(DbConstants.TABLE_MARKET_DATA, null, cv);
	}

//	public void testPortfolioName() {
//
//		try {
//			ContentValues cv = new ContentValues();
//			cv.put(DbConstants.COLUMN_PORTFOLIO_NAME, "Sandamal");
//			database.insert(DbConstants.TABLE_PORTFOLIO_NAME, null, cv);
//		} catch (Exception e) {
//			Log.e(ConstantValues.TAG, e.getMessage());
//		}
//
//		try {
//			String[] arr = { DbConstants.COLUMN_PORTFOLIO_ID,
//					DbConstants.COLUMN_PORTFOLIO_NAME };
//			Cursor c = database.query(DbConstants.TABLE_PORTFOLIO_NAME, arr,
//					null, null, null, null, null);
//			c.moveToFirst();
//			Log.d(ConstantValues.TAG,
//					"portfolio - name - id"
//							+ c.getString(c
//									.getColumnIndex(DbConstants.COLUMN_PORTFOLIO_ID)));
//			Log.d(ConstantValues.TAG,
//					"portfolio - name - name"
//							+ c.getString(c
//									.getColumnIndex(DbConstants.COLUMN_PORTFOLIO_NAME)));
//			c.close();
//		} catch (Exception e) {
//			Log.e(ConstantValues.TAG, e.getMessage());
//		}
//	}



	/**
	 * Delete all entries in the marketdata table
	 */
	public void deleteMarketData() {
		database.delete(DbConstants.TABLE_MARKET_DATA, null, null);
	}

	/**
	 * @return market data to be displayed in the market data window
	 */
	public ArrayList<String[]> getMarketData() {
		ArrayList<String[]> companyData = new ArrayList<String[]>();

		Cursor cursor = database.query(DbConstants.TABLE_MARKET_DATA,
				MARKET_DATA_COL, null, null, null, null, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			String[] strArr = cursorToStringArr(cursor, MARKET_DATA_COL.length);
			companyData.add(strArr);
			cursor.moveToNext();
		}
		cursor.close();
		return companyData;
	}

	/**
	 * converts a cursor into a String[]
	 * 
	 * @param cursor
	 *            the cursor taken from the database
	 * @param arraySize
	 *            the number of data entries
	 * @return return a string[] containing the data entries
	 */
	private String[] cursorToStringArr(Cursor cursor, int arraySize) {

		String[] strArr = new String[arraySize];
		for (int i = 0; i < arraySize; i++) {
			strArr[i] = cursor.getString(i);
		}
		return strArr;
	}

	/**
	 * @param port_id - the id of the portfolio
	 * @return an arraylistt tha contains details of all the companies in the portfolio
	 */
	public ArrayList<String[]> getPortfolioData(int port_id) {

		ArrayList<String[]> portfolioRows = new ArrayList<String[]>();
		Cursor cursor = database.query(DbConstants.TABLE_PORTFOLIO,
				PORTFOLIO_COLUMNS, DbConstants.COLUMN_PORTFOLIO_ID + " = '"
						+ port_id + "'", null, null, null, null);

		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			String[] strArr = cursorToStringArr(cursor,
					PORTFOLIO_COLUMNS.length);
			portfolioRows.add(strArr);
			cursor.moveToNext();
		}
		cursor.close();
		return portfolioRows;
	}

	/**
	 * @return the symbols of all the companies
	 */
	public String[] getAllStockCodes() {
		String[] col = new String[] { DbConstants.COLUMN_SYMBOL };

		Cursor cursor = database.query(DbConstants.TABLE_MARKET_DATA, col,
				null, null, null, null, null);
		String[] stockCodes = new String[cursor.getCount()];
		cursor.moveToFirst();
		int i = 0;
		while (!cursor.isAfterLast()) {
			stockCodes[i] = cursor.getString(0);
			cursor.moveToNext();
			i++;
		}
		cursor.close();
		return stockCodes;

	}

	/**
	 * @param portfolioID - THe id of the portfolio
	 * @param stock - symbol of the company
	 * @param quantity - amount of shares
	 * @param cost - the cost of the transaction
	 * @return
	 */
	public boolean insertToPortfolio(int portfolioID, String stock,
			int quantity, double cost) {
		String[] col = new String[] { DbConstants.COLUMN_QUANTITY,
				DbConstants.COLUMN_COST };
		Cursor cursor = database.query(DbConstants.TABLE_PORTFOLIO, col,
				DbConstants.COLUMN_STOCK + " = '" + stock + "' AND "
						+ DbConstants.COLUMN_PORTFOLIO_ID + " = '"
						+ portfolioID + "'", null, null, null, null);
		if (!cursor.moveToFirst()) {
			ContentValues cv = new ContentValues();
			cv.put(DbConstants.COLUMN_PORTFOLIO_ID, portfolioID);
			cv.put(DbConstants.COLUMN_STOCK, stock);
			cv.put(DbConstants.COLUMN_QUANTITY, quantity);
			cv.put(DbConstants.COLUMN_COST, cost);
			if (database.insert(DbConstants.TABLE_PORTFOLIO, null, cv) != -1) {
				return true;
			} else {
				return false;
			}

		} else {
			cursor.moveToFirst();
			quantity = cursor.getInt(0) + quantity;
			cost = cursor.getFloat(1) + cost;
			cursor.close();
			ContentValues cv = new ContentValues();
			cv.put(DbConstants.COLUMN_QUANTITY, quantity);
			cv.put(DbConstants.COLUMN_COST, cost);

			if (database.updateWithOnConflict(DbConstants.TABLE_PORTFOLIO, cv,
					DbConstants.COLUMN_STOCK + " = '" + stock + "' AND "
							+ DbConstants.COLUMN_PORTFOLIO_ID + " = '"
							+ portfolioID + "'", null,
					SQLiteDatabase.CONFLICT_REPLACE) != 0) {
				return true;
			} else {
				return false;
			}
		}
	}

	/**
	 * @param portfolioID
	 * @return data to be displayed on the portfolio page
	 */
	public ArrayList<String[]> getPortDisplayData(int portfolioID) {
		ArrayList<String[]> rowList = new ArrayList<String[]>();
		String sql = "SELECT " + DbConstants.COLUMN_SYMBOL + ","
				+ DbConstants.COLUMN_COMPANY_NAME + ","
				+ DbConstants.COLUMN_LAST_TRADED_PRICE + ","
				+ DbConstants.COLUMN_CHANGE_RS + ","
				+ DbConstants.COLUMN_CHANGE_PRECENTAGE + " FROM "
				+ DbConstants.TABLE_MARKET_DATA + ",(SELECT "
				+ DbConstants.COLUMN_STOCK + " FROM "
				+ DbConstants.TABLE_PORTFOLIO + " WHERE "
				+ DbConstants.COLUMN_PORTFOLIO_ID + " = 1) AS T WHERE "
				+ DbConstants.TABLE_MARKET_DATA + "."
				+ DbConstants.COLUMN_SYMBOL + " = T."
				+ DbConstants.COLUMN_STOCK + "  GROUP BY "
				+ DbConstants.COLUMN_SYMBOL;
		Cursor cursor = database.rawQuery(sql, null);

		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			String[] strArr = cursorToStringArr(cursor, MARKET_DATA_COL.length);
			rowList.add(strArr);
			cursor.moveToNext();
		}
		cursor.close();
		return rowList;

	}

	/**
	 * @param portfolioID
	 * @param stock - company symbol
	 * @param quantityNum - the number of shares
	 * @param cost = total cost
	 */
	public void addTransaction(int portfolioID, String stock, int quantityNum,
			double cost) {
		ContentValues cv = new ContentValues();
		cv.put(DbConstants.COLUMN_STOCK, stock);
		cv.put(DbConstants.COLUMN_TYPE, DbConstants.BUY);
		cv.put(DbConstants.COLUMN_QUANTITY, quantityNum);
		cv.put(DbConstants.COLUMN_COST, cost);
		long transactionRow = database.insert("`"
				+ DbConstants.TABLE_TRANSACTION + "`", null, cv);
		Log.d(ConstantValues.TAG, "row id " + transactionRow);
	}

	/**
	 * @param portfolioID
	 * @param transactionRow - the last row of the transaction table
	 */
	private void addPortfolioTransaction(int portfolioID, float transactionRow) {

		String sql = "INSERT INTO `" + DbConstants.TABLE_TRANSACTION_PORTFOLIO
				+ "` ( `" + DbConstants.COLUMN_TRANSACTION_ID + "` , `"
				+ DbConstants.COLUMN_PORTFOLIO_ID + "`) VALUES ((SELECT `"
				+ DbConstants.COLUMN_TRANSACTION_ID + "` FROM `"
				+ DbConstants.TABLE_TRANSACTION + "` WHERE `"
				+ DbConstants.ROW_ID + "` = '" + transactionRow + "' ), '"
				+ portfolioID + "')";
		database.rawQuery(sql, null);
	}

	/**
	 * @param portfolioID
	 * @return the total value of the portfolio
	 */
	public double[] getTotalValue(int portfolioID) {
		double[] results = new double[2];
		String sql = "SELECT SUM(portfolio.quantity*market_data.last_traded_price) FROM portfolio,market_data WHERE portfolio.stock = market_data.symbol";
		String sql1 = "select SUM(cost) FROM portfolio";

		Cursor c1 = database.rawQuery(sql, null);
		c1.moveToFirst();
		results[0] = c1.getFloat(0);
		c1.close();

		Cursor c2 = database.rawQuery(sql1, null);
		c2.moveToFirst();
		results[1] = c2.getFloat(0);
		c2.close();
		return results;

	}

	/**
	 * @param companyRow
	 * @return the details of a company to be displayed in the market data window
	 */
	public String[] getCompanyInfo(int companyRow) {
		String sql = "SELECT " + DbConstants.COLUMN_SYMBOL + ","
				+ DbConstants.COLUMN_COMPANY_NAME + ","
				+ DbConstants.COLUMN_OPEN + "," + DbConstants.COLUMN_HIGH + ","
				+ DbConstants.COLUMN_LOW + "," + DbConstants.COLUMN_PREV_CLOSE
				+ "," + DbConstants.COLUMN_LAST_TRADED_PRICE + ","
				+ DbConstants.COLUMN_CHANGE_RS + " FROM "
				+ DbConstants.TABLE_MARKET_DATA + " WHERE rowid = "
				+ companyRow;
		Cursor cursor = database.rawQuery(sql, null);
		cursor.moveToFirst();
		return cursorToStringArr(cursor, cursor.getColumnCount());

	}

	/**
	 * @param symbol
	 * @return the details of a company to be displayed in the portfolio window
	 */
	public String[] getCompanyDetails(String symbol) {

		String sql = "SELECT " + DbConstants.COLUMN_SYMBOL + ","
				+ DbConstants.COLUMN_COMPANY_NAME + ","
				+ DbConstants.COLUMN_OPEN + "," + DbConstants.COLUMN_HIGH + ","
				+ DbConstants.COLUMN_LOW + "," + DbConstants.COLUMN_PREV_CLOSE
				+ "," + DbConstants.COLUMN_LAST_TRADED_PRICE + ","
				+ DbConstants.COLUMN_CHANGE_RS + " FROM "
				+ DbConstants.TABLE_MARKET_DATA + " WHERE "
				+ DbConstants.COLUMN_SYMBOL + " = '" + symbol + "'";
		Cursor c1 = database.rawQuery(sql, null);

		String sql2 = "SELECT (cost/quantity) AS avg_price,quantity,((market_data.last_traded_price-(cost/quantity))*quantity) AS profit FROM   portfolio,market_data WHERE portfolio.stock = 'HHL.N0000' AND portfolio.stock=market_data.symbol;";
		Cursor c2 = database.rawQuery(sql2, null);

		int size1 = c1.getColumnCount();
		int size2 = c2.getColumnCount();
		String[] resultSet = new String[size1 + size2];

		c1.moveToFirst();
		for (int i = 0; i < size1; i++) {
			resultSet[i] = c1.getString(i);
		}

		c2.moveToFirst();
		for (int j = size1; j < size1 + size2; j++) {
			resultSet[j] = c2.getString(j - size1);
		}
		c1.close();
		c2.close();
		return resultSet;
	}

	/**
	 * @return the details of the companies that have the highest precentage gain
	 */
	public ArrayList<String[]> getTopStocks() {
		ArrayList<String[]> companyData = new ArrayList<String[]>();

		Cursor cursor = database.query(DbConstants.TABLE_MARKET_DATA,
				MARKET_DATA_COL, null, null, null, null, "`"
						+ DbConstants.COLUMN_CHANGE_PRECENTAGE
						+ "` DESC LIMIT 10");
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			String[] strArr = cursorToStringArr(cursor, MARKET_DATA_COL.length);
			companyData.add(strArr);
			cursor.moveToNext();
		}
		cursor.close();
		return companyData;
	}

}
